import os, sys
#from PySide2 import QtWidgets, QtPrintSupport, QtCore
from PyQt5 import QtWidgets, QtPrintSupport, QtCore
import CommonFunctions
import main
import xlrd, xlwt, openpyxl
from xlutils import copy
import random
import time
import re
from functools import partial
import win32print
import win32com.client

app = QtWidgets.QApplication(sys.argv)
class MainApplication(main.MainWindow):
    def __init__(self):
        super(MainApplication, self).__init__()
        self.companyName = '公司名'
        #初始化sql server数据库对象
        self.sqlServerObject = CommonFunctions.DBOption()
        self.sqlServerObject.set('dbType', 'SQLSERVER')
        #初始化sqlite数据库对象
        self.initSettingDB('setting.db')
        #字句替换列表
        self.generalStringReplaceList = []
        self.customStringReplaceList = []
        #检查指定路线和走货路线
        self.filterHubin = 0
        self.filterHubout = 0
        self.selectedHubin = []
        self.selectedHubout = []
        #目的地提醒
        self.isDestNameNotify = 0
        self.destNameNotifyList = []
        #查询数据库字段初始值
        self.queryField = 'Jobno'        
        #初始化tts对象
        self.tts = CommonFunctions.TextToSpeech()
        #初始化音频播放
        self.playAudio = CommonFunctions.PlayAudio()
        #每个RadioButton选择项要操作的类型
        self.selectActionDict = {
            'rb3' : self.rb3action, 
            'rb4' : self.rb4action, 
            'rb5' : self.rb5action, 
            'rb6' : self.rb6action, 
            'rb7' : self.rb7action, 
            'rb8' : self.rb8action, 
            'rb9' : self.rb9action, 
            'rb10' : self.rb10action, 
            'rb11' : self.rb11action, 
            'rb12' : self.rb12action, 
            'rb13' : self.rb13action, 
            'rb14' : self.rb14action, 
            'rb15' : self.rb15action            
        }
        #当前选中radioButton
        self.selectAction = 'rb12'
        #当前选中radioButton别名初始值
        self.selectRadioButtonAlias = '出货扫描'
        #数据库字段名和sql返回数据字段顺序映射字典
        self.tableFieldDict = {
            'Jobno' : [0, '原单号'], 
            'Refno' : [1, '转单号'], 
            'Hubin' : [2, '指定路线'], 
            'Hubout' : [3,'走货路线'], 
            'delistation' : [4, '下家代码'], 
            'SDShipp' : [5, '下家名称'],
            'DestNo' : [6, '目的地简码'], 
            'DestName' : [7, '目的地名称'], 
            'IsAuditingJobno' : [8, '是否审单'], 
            'IsAuditingYF' : [9, '是否走货'], 
            'SDStation' : [10, '站点'], 
            'Weig' : [11, '收货重'], 
            'ShipWeig' : [12, '出货重'],             
            'DescrName' : [13, '物品名称'], 
            'Pcs' : [14, '件数']
            }
        #根据所选条件建立分类清单字典
        self.maniClassDict = {
            'Hubin' : self.maniClassCheck, 
            'Hubout' : self.maniClassCheck, 
            'delistation' : self.maniClassCheck, 
            'IsAuditingJobno' : self.maniClassCheck, 
            'IsAuditingYF' : self.maniClassCheck, 
            'SDStation' : self.maniClassCheck, 
            'selectedHubin' : self.maniClassCheckList, 
            'selectedHubout' : self.maniClassCheckList, 
            'destNameNotify' : self.maniClassCheckList
        }
        #comboBox和sqlite数据表及缓存列表对应
        self.checkListDict = {
            'limitHubIn' : [self.selectedHubin, self.comboBox],
            'limitHubOut' : [self.selectedHubout, self.comboBox_2],
            'notifyDest' : [self.destNameNotifyList, self.comboBox_3]
        }
        #设置菜单
        self.menuBar.addAction(self.action)
        self.menuBar.addAction(self.action_2)
        self.menuBar.addAction(self.action_5)
        #是否验证原单与转单号
        self.isCheckNumBind = False
        #当前选中输入框对象
        self.selectLineEdit = self.lineEdit
        self.lineEdit_3.setDisabled(True)
        #是否添加进清单
        self.isAddInManifest = True
        self.maniClassCondi = []        
        #已存在全部数据列表
        self.existedShipmentAll = []
        #已存在清单记录        
        self.classManiList = []
        #分类清单对象列表
        self.classManiObjectList = []
        #分类清单货件数据表
        self.classManiDataList = []
        #读取当前文件夹下的excel文件
        for s1a in os.listdir(os.path.split(os.path.abspath(sys.argv[0]))[0] + "\printtemplate"):
            if not os.path.isdir(s1a):
                if os.path.splitext(s1a)[1] == '.xls' or os.path.splitext(s1a)[1] == '.xlsx':
                    self.comboBox_4.addItem(s1a)
        self.printTemplate = self.comboBox_4.currentText()
        
        #临时文件编号
        self.fileLoopIndex = random.randint(1, 31)
        
        #所有打印机加入combobox
        self.printersList = QtPrintSupport.QPrinterInfo().availablePrinterNames().copy()
        self.sysDefaultPrinter = QtPrintSupport.QPrinterInfo().defaultPrinterName()
        for n1a in range(len(self.printersList)):
            self.comboBox_5.addItem(self.printersList[n1a])
            if self.printersList[n1a] == self.sysDefaultPrinter:
                self.comboBox_5.setCurrentIndex(n1a)
        
        #从数据库读取设置信息
        self.readSettingDBTable('generalStringReplace', self.generalStringReplaceList, 'stringReplace',[0, 1])
        self.readSettingDBTable(self.selectAction, self.customStringReplaceList, 'stringReplace',[0, 1])
        self.readSettingDBTable('limitHubIn', self.checkListDict['limitHubIn'][0],'checkList',[0])
        self.readSettingDBTable('limitHubOut', self.checkListDict['limitHubOut'][0],'checkList',[0])
        self.readSettingDBTable('notifyDest', self.checkListDict['notifyDest'][0], 'checkList',[0])
        self.sqliteObject.set('sqlStatement', 'select * from main_settings;')
        try:
            self.sqliteObject.sqlExecute()            
            for l2a in self.sqliteObject.sqlResult:
                if l2a[0] == 'companyName':
                    self.companyName = l2a[1]
        except:
            pass
        #comboBox填入数据
        for s1a in (self.checkListDict):
            for s2a in self.checkListDict[s1a][0]:
                self.checkListDict[s1a][1].addItem(s2a)
        #焦点切换到输入行
        self.selectLineEdit.setFocus()
        
    def lineEditInputProcess(self, inputType):
        s1a = self.selectLineEdit.text().strip()
        self.label_3.clear()
        self.label_4.clear()
        if len(s1a) < 41:
            if len(s1a) > 0:
                if self.isCheckNumBind == True:
                    if inputType == 'originalSN':
                        if self.inputQueryShipment(s1a) == True:
                            self.radioButton_2.setChecked(True)
                            self.lineEdit_3.setEnabled(True)
                            self.lineEdit.setDisabled(True)
                            self.selectLineEdit = self.lineEdit_3
                            self.queryField = "Refno"                                
                            self.lineEdit_3.setFocus()
                        
                    elif inputType == 'forwardSN':
                        if self.sqlServerObject.sqlResult[0][self.tableFieldDict[self.queryField][0]] == s1a:
                            c6a = 1
                            if self.isAddInManifest == True:
                                if self.addManifest(self.sqlServerObject.sqlResult[0]) == 'alreadyHave':
                                    c6a = 2
                        else:
                            c6a = 0
                        self.lineEdit.clear()
                        self.lineEdit_3.clear()
                        self.radioButton.setChecked(True)
                        self.lineEdit.setEnabled(True)
                        self.lineEdit_3.setDisabled(True)
                        self.selectLineEdit = self.lineEdit
                        self.queryField = "Jobno"
                        if c6a == 1:                            
                            self.playAudio.play()
                        elif c6a == 0:                            
                            self.tts.speak("转单不符")
                        elif c6a == 2:
                            pass
                        self.lineEdit.setFocus()
                else:
                    if self.inputQueryShipment(s1a) == True:
                        self.addManifest(self.sqlServerObject.sqlResult[0])
                    self.selectLineEdit.clear()
            else:
                if self.queryField == "Jobno":
                    self.label_4.setText("请输入原单号")
                elif self.queryField == "Refno":
                    self.label_4.setText("请输入转单号")
        else:
            self.label_4.setText("错误：输入字符超过40")
        
    def rb3action(self, sqlResult):
        if self.generalOperation(sqlResult) == False:
            self.label_4.setText(self.label_4.text() + sqlResult[self.tableFieldDict['Jobno'][0]])
        else:
            self.ttsSpeak(sqlResult, self.tableFieldDict['Jobno'][0], self.customStringReplaceList, self.generalStringReplaceList)
    def rb4action(self, sqlResult):
        if self.generalOperation(sqlResult) == False:
            self.label_4.setText(self.label_4.text() + sqlResult[self.tableFieldDict['Refno'][0]])
        else:
            self.ttsSpeak(sqlResult, self.tableFieldDict['Refno'][0], self.customStringReplaceList, self.generalStringReplaceList)
    def rb5action(self, sqlResult):
        if self.generalOperation(sqlResult) == False:
            self.label_4.setText(self.label_4.text() + sqlResult[self.tableFieldDict['Hubin'][0]])
        else:
            self.ttsSpeak(sqlResult, self.tableFieldDict['Hubin'][0], self.customStringReplaceList, self.generalStringReplaceList)
    def rb6action(self, sqlResult):
        if self.generalOperation(sqlResult) == False:
            self.label_4.setText(self.label_4.text() + sqlResult[self.tableFieldDict['Hubout'][0]])
        else:
            self.ttsSpeak(sqlResult, self.tableFieldDict['Hubout'][0], self.customStringReplaceList, self.generalStringReplaceList)
    def rb7action(self, sqlResult):
        if self.generalOperation(sqlResult) == False:
            self.label_4.setText(self.label_4.text() + sqlResult[self.tableFieldDict['delistation'][0]])
        else:
            self.ttsSpeak(sqlResult, self.tableFieldDict['delistation'][0], self.customStringReplaceList, self.generalStringReplaceList)
    def rb8action(self, sqlResult):
        if self.generalOperation(sqlResult) == False:
            self.label_4.setText(self.label_4.text() + sqlResult[self.tableFieldDict['DestName'][0]])
        else:
            self.ttsSpeak(sqlResult, self.tableFieldDict['DestName'][0], self.customStringReplaceList, self.generalStringReplaceList)
    def rb9action(self, sqlResult):
        s1a = '否'
        s1b = '否'
        if sqlResult[self.tableFieldDict['IsAuditingYF'][0]] == 1:            
            s1b = '是'            
            if sqlResult[self.tableFieldDict['IsAuditingJobno'][0]] == 1:            
                s1a = '是'
            self.tts.speak("已出货")
        else:
            if sqlResult[self.tableFieldDict['IsAuditingJobno'][0]] == 1:
                s1a = '是'
                self.tts.speak("已审单")
            else:
                self.tts.speak("未审单")
        self.label_4.setText('审单：'+s1a+'    出货：'+s1b)
    def rb10action(self, sqlResult):
        if self.generalOperation(sqlResult) == False:
            self.label_4.setText(self.label_4.text() + sqlResult[self.tableFieldDict['Weig'][0]])
        else:
            self.ttsSpeak(sqlResult, self.tableFieldDict['Weig'][0], self.customStringReplaceList, self.generalStringReplaceList)
    def rb11action(self, sqlResult):
        if self.generalOperation(sqlResult) == False:
            self.label_4.setText(self.label_4.text() + sqlResult[self.tableFieldDict['DestName'][0]])
        else:
            self.ttsSpeak(sqlResult, self.tableFieldDict['Hubin'][0], self.customStringReplaceList, self.generalStringReplaceList)
    def rb12action(self, sqlResult):
        if self.generalOperation(sqlResult) == False:
            self.label_4.setText(self.label_4.text() + str(sqlResult[self.tableFieldDict['IsAuditingJobno'][0]]))
        else:
            if sqlResult[self.tableFieldDict['IsAuditingYF'][0]] == True:
                self.tts.speak('已出货')
            elif sqlResult[self.tableFieldDict['IsAuditingJobno'][0]] == True:
                self.ttsSpeak(sqlResult, self.tableFieldDict['Hubout'][0], self.customStringReplaceList, self.generalStringReplaceList)
            else:
                self.tts.speak('未审单')
    def rb13action(self, sqlResult):
        if self.generalOperation(sqlResult) == False:
            self.label_4.setText(self.label_4.text() + sqlResult[10])
        else:
            self.ttsSpeak(sqlResult, self.tableFieldDict['SDStation'][0], self.customStringReplaceList, self.generalStringReplaceList)
    def rb14action(self, sqlResult):        
        if self.generalOperation(sqlResult) == False:
            self.label_4.setText(self.label_4.text() + sqlResult[11])
        else:
            self.ttsSpeak(sqlResult, self.tableFieldDict['Weig'][0], self.customStringReplaceList, self.generalStringReplaceList)
    def rb15action(self, sqlResult):
        s1a = sqlResult[0]
        self.label_4.setText(s1a)
        self.generalOperation(s1a)
    
    def inputQueryShipment(self, queryString):
        self.label_3.setText(queryString)
        s1a = self.sqlServerObject.set('sqlStatement', "select Jobno,Refno,hubin,hubout,\
        delistation,SDShipp,DestNo,DestName,IsAuditingJobno,IsAuditingYF,SDStation,Weig,ShipWeig,DescrName,Pcs \
        from mManifest where " + self.queryField + "='" + queryString + "' order by ID")
        if s1a == True:
            s2a = self.sqlServerObject.sqlExecute()
            if s2a == True:
                n3a = len(self.sqlServerObject.sqlResult)
                if n3a > 1:
                    self.label_4.setText("错误：单号不唯一 " + self.sqlServerObject.sqlResult[n3a - 1])
                    self.tts.speak("错误，单号不唯一")
                elif n3a == 1:
                    self.label_4.setText(str(self.sqlServerObject.sqlResult[0]))
                    self.selectActionDict[self.selectAction](self.sqlServerObject.sqlResult[0])
                    return(True)
                else:
                    self.label_4.setText("无信息")
                    self.tts.speak("无信息")
            else:
                self.label_4.setText("错误：" + s2a)
        else:
            self.label_4.setText("错误：" + s1a)
    
    def generalOperation(self, sqlResult):
        if self.filterHubin == 1:
            if len(self.selectedHubin) > 0:
                c1a = False
                for s2a in self.selectedHubin:
                    if sqlResult[self.tableFieldDict['Hubin'][0]] == s2a:
                        c1a = True
                        break
                if c1a == False:
                    self.label_4.setText("指定路线不符")
                    self.tts.speak("指定路线不符")
                    return(False)
        if self.filterHubout == 1:
            if len(self.selectedHubout) > 0:
                c1b = False
                for s2b in self.selectedHubout:
                    if sqlResult[self.tableFieldDict['Hubout'][0]] == s2b:
                        c1b = True
                        break
                if c1b == False:
                    self.label_4.setText("走货路线不符")
                    self.tts.speak("走货路线不符")
                    return(False)
        if self.isDestNameNotify == 1:
            for s2b in self.destNameNotifyList:
                if sqlResult[self.tableFieldDict['DestName'][0]] == s2b:
                    self.tts.speak(s2b)
                    break
    
    def addManifest(self, sqlResult):
        #检查输入单号是否已存在
        c2a = False
        #是否需要加入分类清单
        c2b = True
        for i3a, l3a in enumerate(self.existedShipmentAll):
            if l3a[self.tableFieldDict['Jobno'][0]] == sqlResult[self.tableFieldDict['Jobno'][0]]:
                c2a = True
                break
        if c2a == True:
            #审单状态改变
            c3a = False
            #出货状态改变
            c3b = False
            ls3a = '已出货'
            #如果出货状态未变
            if sqlResult[self.tableFieldDict['IsAuditingYF'][0]] == l3a[self.tableFieldDict['IsAuditingYF'][0]]:
                #如果审单状态未变
                if sqlResult[self.tableFieldDict['IsAuditingJobno'][0]] == l3a[self.tableFieldDict['IsAuditingJobno'][0]]:
                    self.tts.speak("有重复")
                    return('alreadyHave')
                #如果审单状态改变为未审单
                elif l3a[self.tableFieldDict['IsAuditingJobno'][0]] == 1:
                    c3a = True
                    self.existedShipmentAll[i3a][self.tableFieldDict['IsAuditingJobno'][0]] = 0
                    if l3a[self.tableFieldDict['IsAuditingYF'][0]] == 0:
                        self.tableWidget.setItem(i3a, 7, QtWidgets.QTableWidgetItem('未审单'))
                        ls3a = '未审单'
                    self.tts.speak("变成未审单")
                #如果审单状态改变为已审单
                else:
                    c3a = True
                    self.existedShipmentAll[i3a][self.tableFieldDict['IsAuditingJobno'][0]] = 1
                    if l3a[self.tableFieldDict['IsAuditingYF'][0]] == 0:
                        self.tableWidget.setItem(i3a, 7, QtWidgets.QTableWidgetItem('已审单'))
                        ls3a = '已审单'
                    self.tts.speak("变成已审单")
            #如果出货状态改变
            else:
                c3b = True
                #是否变为已出货状态
                c4a = False
                #出货状态变为未出货 
                if l3a[self.tableFieldDict['IsAuditingYF'][0]] == 1:
                    self.existedShipmentAll[i3a][self.tableFieldDict['IsAuditingYF'][0]] = 0
                    self.tts.speak("变成未出货")
                #出货状态变为已出货
                else:
                    self.existedShipmentAll[i3a][self.tableFieldDict['IsAuditingYF'][0]] = 1
                    self.tableWidget.setItem(i3a, 7, QtWidgets.QTableWidgetItem('已出货'))
                    ls3a = '已出货'
                    c4a = True                        
                    self.tts.speak("变成已出货")
                #如果审单状态改变
                if sqlResult[self.tableFieldDict['IsAuditingJobno'][0]] != l3a[self.tableFieldDict['IsAuditingJobno'][0]]:
                    c3a = True
                    #审单状态变为未审单
                    if l3a[self.tableFieldDict['IsAuditingJobno'][0]] == 1:
                        self.existedShipmentAll[i3a][self.tableFieldDict['IsAuditingJobno'][0]] = 0
                        if c4a == False:
                            self.tableWidget.setItem(i3a, 7, QtWidgets.QTableWidgetItem('未审单'))
                            ls3a = '未审单'
                        self.tts.speak("未审单")
                    #审单状态变为已审单
                    else:
                        self.existedShipmentAll[i3a][self.tableFieldDict['IsAuditingJobno'][0]] = 1
                        if c4a == False:
                            self.tableWidget.setItem(i3a, 7, QtWidgets.QTableWidgetItem('已审单'))
                            ls3a = '已审单'
                        self.tts.speak("已审单")
            if c3a == False or c3b == False:
                #遍历分类清单数据表list
                for n3a, l3b in enumerate(self.classManiDataList):
                    #遍历每个分类清单数据表中的每行数据
                    for n4a, l4a in enumerate(l3b):
                        #匹配原单号
                        if l4a[self.tableFieldDict['Jobno'][0]] == sqlResult[self.tableFieldDict['Jobno'][0]]:
                            c2b = False
                            #检查是否含货件状态的表名称，是则删除记录；否则修改
                            for l7a in self.classManiList[n3a]:
                                if c3a == False:
                                    if l7a[0] == self.tableFieldDict['IsAuditingJobno'][1]:                                            
                                        c2b = True
                                elif c3b == False:
                                    if l7a[0] == self.tableFieldDict['IsAuditingYF'][1]:
                                        c2b = True
                            if c2b == True:
                                #显示新总件数
                                self.classManiObjectList[n3a].findChildren(QtWidgets.QLabel)[2].setText(\
                                    str(int(self.classManiObjectList[n3a].findChildren(QtWidgets.QLabel)[2].text())\
                                    - int(sqlResult[self.tableFieldDict['Pcs'][0]])))
                                del l3b[n4a]
                                self.classManiObjectList[n3a].findChild(QtWidgets.QTableWidget).removeRow(n4a)
                                for n8a in range(n4a, len(l3b)):
                                    self.classManiObjectList[n3a].findChild(QtWidgets.QTableWidget).setItem(n8a, 0, QtWidgets.QTableWidgetItem(str(n4a+1)))
                            else:
                                l4a[self.tableFieldDict['IsAuditingJobno'][1]] = sqlResult[self.tableFieldDict['IsAuditingJobno'][1]]
                                l4a[self.tableFieldDict['IsAuditingYF'][1]] = sqlResult[self.tableFieldDict['IsAuditingYF'][1]]
                                self.classManiObjectList[n3a].findChild(QtWidgets.QTableWidget).item(n4a, 7).setText(ls3a)
                            break
        #无重复则加入总清单
        else:
            self.tableWidget.resizeColumnsToContents()
            n3a = self.tableWidget.rowCount()                
            self.tableWidget.insertRow(n3a)
            self.tableWidget.setItem(n3a, 0, QtWidgets.QTableWidgetItem(str(n3a + 1)))
            self.tableWidget.setItem(n3a, 1, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Jobno'][0]]))
            self.tableWidget.setItem(n3a, 2, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Refno'][0]]))
            self.tableWidget.setItem(n3a, 3, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Hubin'][0]]))
            self.tableWidget.setItem(n3a, 4, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Hubout'][0]]))
            self.tableWidget.setItem(n3a, 5, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['delistation'][0]]))
            self.tableWidget.setItem(n3a, 6, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['DestName'][0]]))
            s3a = "未知"
            if sqlResult[self.tableFieldDict['IsAuditingYF'][0]] == 1:
                s3a = "已出货"
            elif sqlResult[self.tableFieldDict['IsAuditingJobno'][0]] == 1:
                s3a = "已审单"
            else:
                s3a = "未审单"
            self.tableWidget.setItem(n3a, 7, QtWidgets.QTableWidgetItem(s3a))
            self.tableWidget.setItem(n3a, 8, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['SDStation'][0]]))
            self.tableWidget.setItem(n3a, 9, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['Pcs'][0]])))
            self.tableWidget.setItem(n3a, 10, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['Weig'][0]])))
            self.tableWidget.setItem(n3a, 11, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['ShipWeig'][0]])))
            self.tableWidget.setItem(n3a, 12, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['DescrName'][0]])))
            self.label_12.setText(str(int(self.label_12.text()) + int(sqlResult[self.tableFieldDict['Pcs'][0]])))
            self.existedShipmentAll.append(sqlResult)
        if c2b == True:
            #按选定清单分类条件查询并返回数据并组成list
            l2a = []
            for l3a in self.maniClassCondi:
                u3a = self.maniClassDict[l3a[0]](sqlResult, l3a[0])
                if type(u3a) != None:
                    #生成分类清单表名称字符串list
                    l2a.append([l3a[1], u3a])
            #检查是否已有相同数值的分类清单
            n2a = len(l2a)
            if n2a > 0:
                c3a = False
                #分类清单list样式[...,[...,['下家代码','56'],...],...]一维代表每个分类清单，二维为每个清单对比条件，三维为对比条件名和值
                for i3a, l3a in enumerate(self.classManiList):                    
                    if n2a == len(l3a):
                        c5a = True
                        for l6a in l2a:
                            if l6a not in l3a:
                                c5a = False
                                break
                        if c5a == False:
                            continue
                        else:
                            c3a = True                            
                            #加入已有清单中
                            n6b = self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).rowCount()
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).insertRow(n6b)
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 0, QtWidgets.QTableWidgetItem(str(n6b + 1)))
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 1, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Jobno'][0]]))
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 2, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Refno'][0]]))
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 3, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Hubin'][0]]))
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 4, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Hubout'][0]]))
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 5, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['delistation'][0]]))
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 6, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['DestName'][0]]))
                            s4a = "未知"
                            if sqlResult[self.tableFieldDict['IsAuditingYF'][0]] == 1:
                                s6a = "已出货"
                            elif sqlResult[self.tableFieldDict['IsAuditingJobno'][0]] == 1:
                                s6a = "已审单"
                            else:
                                s6a = "未审单"
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 7, QtWidgets.QTableWidgetItem(s6a))
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 8, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['SDStation'][0]]))
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 9, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['Pcs'][0]])))
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 10, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['Weig'][0]])))
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 11, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['ShipWeig'][0]])))
                            self.classManiObjectList[i3a].findChild(QtWidgets.QTableWidget).setItem(n6b, 12, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['DescrName'][0]])))
                            #显示新总件数
                            self.classManiObjectList[i3a].findChildren(QtWidgets.QLabel)[2].setText(\
                            str(int(self.classManiObjectList[i3a].findChildren(QtWidgets.QLabel)[2].text())\
                            + int(sqlResult[self.tableFieldDict['Pcs'][0]])))
                            
                            self.classManiDataList[i3a].append(sqlResult)
                            break
                    else:
                        continue
                if c3a == False:
                    #新建分类清单
                    self.classManiList.append(l2a)
                    self.classManiDataList.append([sqlResult])
                    n4a = len(self.classManiObjectList)
                    if n4a == 0:
                        self.groupBox_2.setLayout(QtWidgets.QVBoxLayout())
                    self.classManiObjectList.append(QtWidgets.QGroupBox(self.groupBox_2))
                    self.groupBox_2.layout().addWidget(self.classManiObjectList[n4a])
                    self.classManiObjectList[n4a].setMinimumHeight(200)
                    s4a = ""
                    for l4a in l2a:
                        s4a = s4a + "[" + str(l4a[0]) + ":" + str(l4a[1]).replace("False", "否").replace("True", "是") + "] "
                    self.classManiObjectList[n4a].setLayout(QtWidgets.QVBoxLayout())
                    
                    #添加表格名称及件数行
                    self.classManiObjectList[n4a].layout().addLayout(QtWidgets.QHBoxLayout())
                    
                    label4a = QtWidgets.QLabel(self.classManiObjectList[n4a])
                    label4a.setText(s4a)                    
                    self.classManiObjectList[n4a].layout().findChild(QtWidgets.QHBoxLayout).addWidget(label4a)
                    
                    spacerItem4a = QtWidgets.QSpacerItem(60, 20, QtWidgets.QSizePolicy.Maximum)
                    self.classManiObjectList[n4a].layout().findChild(QtWidgets.QHBoxLayout).addItem(spacerItem4a)
                    
                    label4b = QtWidgets.QLabel(self.classManiObjectList[n4a])
                    label4b.setText("件数")
                    self.classManiObjectList[n4a].layout().findChild(QtWidgets.QHBoxLayout).addWidget(label4b)
                    
                    label4c = QtWidgets.QLabel(self.classManiObjectList[n4a])
                    label4c.setText("0")
                    self.classManiObjectList[n4a].layout().findChild(QtWidgets.QHBoxLayout).addWidget(label4c)
                    
                    spacerItem4b = QtWidgets.QSpacerItem(300, 20, QtWidgets.QSizePolicy.Expanding)
                    self.classManiObjectList[n4a].layout().findChild(QtWidgets.QHBoxLayout).addItem(spacerItem4b)
                    
                    #添加横向布局控件
                    self.classManiObjectList[n4a].layout().addLayout(QtWidgets.QHBoxLayout())
                    
                    classManiButton = QtWidgets.QPushButton(self.classManiObjectList[n4a])
                    classManiButton.setText("复制原单号")
                    classManiButton.clicked.connect(partial(self.writeToClipboard, self.classManiDataList[-1], [0]))
                    self.classManiObjectList[n4a].layout().findChildren(QtWidgets.QHBoxLayout)[1].addWidget(classManiButton)
                    classManiButton = QtWidgets.QPushButton(self.classManiObjectList[n4a])
                    classManiButton.setText("复制全表")
                    classManiButton.clicked.connect(partial(self.writeToClipboard,\
                        self.classManiDataList[-1], [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14]))
                    self.classManiObjectList[n4a].layout().findChildren(QtWidgets.QHBoxLayout)[1].addWidget(classManiButton)
                    classManiButton = QtWidgets.QPushButton(self.classManiObjectList[n4a])
                    classManiButton.setText("导出表格文件")
                    classManiButton.clicked.connect(partial(self.saveAsExcel,self.classManiDataList[-1], self.classManiList[-1]))
                    self.classManiObjectList[n4a].layout().findChildren(QtWidgets.QHBoxLayout)[1].addWidget(classManiButton)
                    classManiButton = QtWidgets.QPushButton(self.classManiObjectList[n4a])
                    classManiButton.setText("打印此表")
                    classManiButton.clicked.connect(partial(self.maniClassPrint, self.classManiDataList[-1]))
                    self.classManiObjectList[n4a].layout().findChildren(QtWidgets.QHBoxLayout)[1].addWidget(classManiButton)
                    classManiButtonDel = QtWidgets.QPushButton(self.classManiObjectList[n4a])
                    classManiButtonDel.setText("删除此表")                        
                    self.classManiObjectList[n4a].layout().findChildren(QtWidgets.QHBoxLayout)[1].addWidget(classManiButtonDel)
                    classManiLabel = QtWidgets.QLabel(self.classManiObjectList[n4a])
                    classManiLabel.setText("查找单号:")                        
                    self.classManiObjectList[n4a].layout().findChildren(QtWidgets.QHBoxLayout)[1].addWidget(classManiLabel)
                    classManiLine = QtWidgets.QLineEdit(self.classManiObjectList[n4a])
                    classManiLine.setMinimumWidth(200)                        
                    self.classManiObjectList[n4a].layout().findChildren(QtWidgets.QHBoxLayout)[1].addWidget(classManiLine)                        
                    classManiSpacer = QtWidgets.QSpacerItem(300, 20, QtWidgets.QSizePolicy.Maximum, QtWidgets.QSizePolicy.Minimum)
                    self.classManiObjectList[n4a].layout().findChildren(QtWidgets.QHBoxLayout)[1].addItem(classManiSpacer)
                    #表格格式
                    classManiTable = QtWidgets.QTableWidget(self.classManiObjectList[n4a])
                    classManiButtonDel.clicked.connect(partial(self.delClasslManifest,classManiTable))
                    classManiLine.returnPressed.connect(partial(self.findAndSelectTableWidgetRow,\
                        classManiTable, self.classManiDataList[-1], self.tableFieldDict['Jobno'][0]))
                    self.classManiObjectList[n4a].layout().addWidget(classManiTable)
                    classManiTable.setColumnCount(13)
                    classManiTable.setHorizontalHeaderItem(0, QtWidgets.QTableWidgetItem("序号"))
                    classManiTable.setHorizontalHeaderItem(1, QtWidgets.QTableWidgetItem(self.tableFieldDict['Jobno'][1]))
                    classManiTable.setHorizontalHeaderItem(2, QtWidgets.QTableWidgetItem(self.tableFieldDict['Refno'][1]))
                    classManiTable.setHorizontalHeaderItem(3, QtWidgets.QTableWidgetItem(self.tableFieldDict['Hubin'][1]))
                    classManiTable.setHorizontalHeaderItem(4, QtWidgets.QTableWidgetItem(self.tableFieldDict['Hubout'][1]))
                    classManiTable.setHorizontalHeaderItem(5, QtWidgets.QTableWidgetItem(self.tableFieldDict['delistation'][1]))
                    classManiTable.setHorizontalHeaderItem(6, QtWidgets.QTableWidgetItem(self.tableFieldDict['DestName'][1]))
                    classManiTable.setHorizontalHeaderItem(7, QtWidgets.QTableWidgetItem("货件状态"))
                    classManiTable.setHorizontalHeaderItem(8, QtWidgets.QTableWidgetItem(self.tableFieldDict['SDStation'][1]))
                    classManiTable.setHorizontalHeaderItem(9, QtWidgets.QTableWidgetItem(self.tableFieldDict['Pcs'][1]))
                    classManiTable.setHorizontalHeaderItem(10, QtWidgets.QTableWidgetItem(self.tableFieldDict['Weig'][1]))
                    classManiTable.setHorizontalHeaderItem(11, QtWidgets.QTableWidgetItem(self.tableFieldDict['ShipWeig'][1]))
                    classManiTable.setHorizontalHeaderItem(12, QtWidgets.QTableWidgetItem(self.tableFieldDict['DescrName'][1]))
                    #表格内容
                    n4b = self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).rowCount()
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).insertRow(n4b)
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 0, QtWidgets.QTableWidgetItem(str(n4b + 1)))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 1, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Jobno'][0]]))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 2, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Refno'][0]]))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 3, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Hubin'][0]]))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 4, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['Hubout'][0]]))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 5, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['delistation'][0]]))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 6, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['DestName'][0]]))
                    s4a = "未知"
                    if sqlResult[self.tableFieldDict['IsAuditingYF'][0]] == 1:
                        s4a = "已出货"
                    elif sqlResult[self.tableFieldDict['IsAuditingJobno'][0]] == 1:
                        s4a = "已审单"
                    else:
                        s4a = "未审单"
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 7, QtWidgets.QTableWidgetItem(s4a))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 8, QtWidgets.QTableWidgetItem(sqlResult[self.tableFieldDict['SDStation'][0]]))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 9, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['Pcs'][0]])))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 10, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['Weig'][0]])))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 11, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['ShipWeig'][0]])))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).setItem(n4b, 12, QtWidgets.QTableWidgetItem(str(sqlResult[self.tableFieldDict['DescrName'][0]])))
                    self.classManiObjectList[n4a].findChild(QtWidgets.QTableWidget).resizeColumnsToContents()
                    self.classManiObjectList[n4a].findChildren(QtWidgets.QLabel)[2].setText(\
                        str(int(self.classManiObjectList[n4a].findChildren(QtWidgets.QLabel)[2].text())+int(sqlResult[self.tableFieldDict['Pcs'][0]])))
    
    def maniClassCheck(self, sqlResult, resultKey):
        n1a = self.tableFieldDict[resultKey][0]
        if n1a != None:
            return(sqlResult[n1a])
        else:
            return(None)
            
    def maniClassCheckList(self, specifiedList):
        pass
        
    def exportToExcel(self, sourceDataList, excelFile, *savePath):
        columnMappingDict = {}
        isFindSettingSheet = True
        fileExtName = os.path.splitext(excelFile)[1]
        def colNameTransNum(columnName):
            columnName = columnName.upper()
            columnIndex = 0
            power = 0
            for n2a in range(len(columnName)-1, -1, -1):
                columnIndex += (ord(columnName[n2a]) - 64) * (26 ** power)
                power += 1
            return(columnIndex)
        def numTransColName(columnNumber):
            power = 0
            dividend = columnNumber
            l2a = []
            while dividend > 0:
                remainder = (dividend - 26 ** power) % (26 ** (power + 1))                        
                l2a.append(chr(remainder // (26 ** power) + 1 + 64))                        
                dividend -= remainder + 26 ** power
                power += 1
            s2a = ''
            l2a.reverse()
            for s2b in l2a:
                s2a = s2a + s2b
            return(s2a)
            
        try:
            if fileExtName == '.xls':
                workbook = xlrd.open_workbook('printtemplate\\' + excelFile, formatting_info=True)
                settingSheet = workbook.sheet_by_name('columnsrule')
                worksheet = workbook.sheets()[0]
                titleCellRange = settingSheet.cell(2, 1).value.upper()
                isTitleRewrite = settingSheet.cell(3, 1).value
                if len(isTitleRewrite) > 0:
                    isTitleRewrite = isTitleRewrite[0].upper()
                else:
                    isTitleRewrite = 'Y'
                writingRow = settingSheet.cell(4, 1).value
                startColumnPrint = settingSheet.cell(6, 1).value
                endColumnPrint = settingSheet.cell(7, 1).value
                settingSheetRows = settingSheet.nrows
                getColumnAValue = lambda rowIndex:settingSheet.cell(rowIndex - 1, 0).value
                getColumnBValue = lambda rowIndex:settingSheet.cell(rowIndex - 1, 1).value
                newWorkbook = copy.copy(workbook)
                worksheet = newWorkbook.get_sheet(0)
                borders = xlwt.Borders()
                borders.left = 1
                borders.right = 1
                borders.top = 1
                borders.bottom = 1
                style = xlwt.XFStyle()
                style.borders = borders
                def setCellValue(rowIndex, columnName, cellValue):
                    columnIndex = colNameTransNum(columnName)
                    worksheet.write(int(rowIndex) - 1, columnIndex - 1, cellValue, style)
                def excelSaveAs(filePath, fileName):
                    if len(filePath) > 0:
                        newWorkbook.save(filePath + '\\' + fileName + '.xls')
                    else:
                        newWorkbook.save(fileName + '.xls')
                    return(filePath + fileName + '.xls')
            elif fileExtName == '.xlsx':
                workbook = openpyxl.load_workbook('printtemplate\\' + excelFile)
                settingSheet = workbook['columnsrule']
                worksheet = workbook[workbook.get_sheet_names()[0]]
                borders = openpyxl.styles.Border(\
                    left=openpyxl.styles.Side(border_style='thin', color='FF000000'),
                    right=openpyxl.styles.Side(border_style='thin', color='FF000000'),
                    top=openpyxl.styles.Side(border_style='thin', color='FF000000'),
                    bottom=openpyxl.styles.Side(border_style='thin', color='FF000000'))
                titleCellRange = settingSheet["B3"].value.upper()
                isTitleRewrite = settingSheet["B4"].value
                if len(isTitleRewrite) > 0:
                    isTitleRewrite = isTitleRewrite[0].upper()
                else:
                    isTitleRewrite = 'Y'
                writingRow = settingSheet["B5"].value
                startColumnPrint = settingSheet["B7"].value
                endColumnPrint = settingSheet["B8"].value
                settingSheetRows = settingSheet.max_row
                getColumnAValue = lambda rowIndex:settingSheet['A' + str(rowIndex)].value
                getColumnBValue = lambda rowIndex:settingSheet['B' + str(rowIndex)].value
                worksheet = workbook[workbook.get_sheet_names()[0]]
                def setCellValue(rowIndex, columnName, cellValue):
                    worksheet[columnName + str(rowIndex)] = cellValue                    
                def excelSaveAs(filePath, fileName):
                    workbook._active_sheet_index = 0
                    workbook.remove_sheet(settingSheet)
                    if len(filePath) > 0:
                        workbook.save(filePath + '\\' + fileName + '.xlsx')
                    else:
                        workbook.save(fileName + '.xlsx')
                    workbook.close()
                    return(filePath + fileName + '.xlsx')
        except FileNotFoundError as erro:
            QtWidgets.QMessageBox.warning(self.centralWidget,'文件未找到','找不到指定模板文件，请重新指定模板文件!', QtWidgets.QMessageBox.Ok)
            return(False)            
        #openpyxl未找到工作表错误
        except KeyError as erro:
            if str(erro).find("not exist") != -1:
                isFindSettingSheet = False
        #xlrd未找到工作表错误        
        except xlrd.biffh.XLRDError as erro:
            if str(xlrd.biffh.XLRDError).find("No sheet") != -1:
                isFindSettingSheet = False
        else:
            m2a = re.match('^([A-Z]+)(\d+)$', titleCellRange)
            if type(m2a) != None:                    
                titleRow = m2a.group(2)
                titleColumn = m2a.group(1)
            else:
                titleRow = "1"
                titleColumn = "A"
            #读取设置工作表数据列映射参数
            for i3a in range(12, settingSheetRows + 1):
                #读取数据导出到表格哪列参数
                s4a = getColumnBValue(i3a)
                if s4a != None:
                    s4a = s4a.strip()
                    if len(s4a) > 0:
                        #检查数据字段名是否有效
                        s5a = getColumnAValue(i3a)
                        if s5a == "序号":
                            snColumn = s4a.upper()
                        elif s5a != None:
                            s5a = s5a.strip()
                            if len(s5a) > 0:
                                for s5b in self.tableFieldDict:
                                    if s5a == self.tableFieldDict[s5b][1]:
                                        columnMappingDict[s4a.upper()] = self.tableFieldDict[s5b][0]
                                        if s5b == 'Pcs':
                                            pcsColumn = s4a.upper()
                                        elif s5b == 'ShipWeig':
                                            weigColumn = s4a.upper()
                                        break
        if isFindSettingSheet == False:
            messageBox = QtWidgets.QMessageBox(self.centralWidget)
            messageBox.setWindowTitle("未找到工作表")
            messageBox.setText("未在模板工作薄中找到'mappingrule'工作表。\n无法确定数据在工作表中起始位置及对应列。\n是否现在开始设置？")
            button1 = messageBox.addButton("现在设置", QtWidgets.QMessageBox.ActionRole)
            button2 = messageBox.addButton("使用默认", QtWidgets.QMessageBox.ActionRole)
            if messageBox.clickedButton == button1:
                self.setMappingRule(workbook)
            elif messageBox.clickedButton == button2:
                writingRow = 3
                d3a = {\
                    '序号' : 'A', 
                    '原单号' : 'B', 
                    '转单号' : 'C', 
                    '走货路线' : 'D', 
                    '目的地名称' : 'E',
                    '件数' : 'F', 
                    '出货重' : 'G', 
                    '物品名称' : 'H'                        
                }
                pcsColumn = d3a['件数']
                weigColumn = d3a['出货重']
                for s3a in d3a:
                    for s4a in self.tableFieldDict:
                        if s3a == self.tableFieldDict[s4a][1]:
                            columnMappingDict[d3a[s3a]] = self.tableFieldDict[s4a][0]
                            break
                titleRow = "1"
                titleColumn = "A"
                snColumn = "A"
            messageBox.show()
        if len(columnMappingDict) > 0:
            boderStartColumn = colNameTransNum(startColumnPrint)
            boderEndColumn = colNameTransNum(endColumnPrint)
            #开始写入工作表
            totalPcs = 0
            totalWeight = 0
            receiver = {}
            for n2a in range(len(sourceDataList)):
                for s3a in columnMappingDict:
                    setCellValue(writingRow + n2a, s3a, sourceDataList[n2a][columnMappingDict[s3a]])
                    #统计件数及重量
                    if s3a == pcsColumn:
                        totalPcs += int(sourceDataList[n2a][columnMappingDict[s3a]])
                    elif s3a == weigColumn:
                        totalWeight += float(sourceDataList[n2a][columnMappingDict[s3a]])
                    #统计同一公司名出现的次数
                        try:
                            receiver[sourceDataList[n2a][self.tableFieldDict['SDShipp'][0]]] += 1
                        except KeyError:
                            receiver[sourceDataList[n2a][self.tableFieldDict['SDShipp'][0]]] = 1
                #画框线
                for n3a in range(boderStartColumn - 1, boderEndColumn):
                    if fileExtName == '.xls':
                            pass
                    elif fileExtName == '.xlsx':                       
                        worksheet[numTransColName(n3a + 1) + str(writingRow + n2a)].border = borders
                #写入序号
                setCellValue(writingRow + n2a, snColumn, str(n2a + 1))
            #统计哪个公司名出现次数最多
            n2a = 0
            s2a = ''
            for s2b in receiver:
                if receiver[s2b] > n2a:
                    s2a = s2b
                    n2a = receiver[s2b]
            #写入标题
            if isTitleRewrite == 'Y':
                setCellValue(titleRow, titleColumn, self.companyName + "交" + s2a + "清单    " + time.strftime('%m-%d',time.localtime(time.time())))
            #写入总件数及重量
            setCellValue(writingRow + n2a + 2, pcsColumn, totalPcs)
            setCellValue(writingRow + n2a + 2, weigColumn, totalWeight)
            #保存            
            try:
                if len(savePath) > 0:
                    s4a = savePath[0]
                    s4b = re.sub('(\.xls)|(\.xlsx)', '', savePath[1])
                else:
                    s4a = 'printtemplate\\temp\\'
                    s4b = 'temp' + str(self.fileLoopIndex)
                    self.fileLoopIndex = self.fileLoopIndex % 31 + 1
                return(excelSaveAs(s4a, s4b))
            except PermissionError as erro:
                if str(erro).find("Permission denied") != -1:
                    QtWidgets.QMessageBox.warning(self.centralWidget,'文件权限错误','请确认将要存储文件处于未打开状态并可写入', QtWidgets.QMessageBox.Ok)
                    return(False)
            return(True)
        else:
            return(False)

    def saveAsExcel(self, datasList, tableNameList):
        #获取上次文件保存路径
        self.sqliteObject.set('sqlStatement', "select setting_value from main_settings where setting_name = 'excelSaveAsPath';")
        filePath = ''
        #表格后缀名
        fileExtName = os.path.splitext(self.printTemplate)[1]
        try:
            self.sqliteObject.sqlExecute()
            filePath = self.sqliteObject.sqlResult[0][0]
        except:
            pass
        #把表格名称字符串拼成文件名
        for n1a, l2a in enumerate(tableNameList):
            if n1a == 0:
                tableName = str(l2a[0]) + '_' + str(l2a[1])
            else:
                tableName = tableName + '_' + str(l2a[0]) + '_' + str(l2a[1])
        #文件保存对话框文件名后缀过滤
        fileFilter = 'all(*)'
        if fileExtName == '.xlsx':
            fileFilter = '10格式(*.xlsx)'
        elif fileExtName == '.xls':
            fileFilter = '03格式(*.xls)'
        #获取文件保存对话框输入的路径及文件名。
        if len(filePath) > 0:
            t1a = QtWidgets.QFileDialog.getSaveFileName(caption='另存为',\
                directory=filePath + '//' + str(time.strftime('%m_%d',time.localtime(time.time()))) + tableName, \
                filter=fileFilter)
            filePath = os.path.split(t1a[0])[0]
            if len(filePath) > 0:
                self.sqliteObject.set('sqlStatement', "update main_settings set setting_value = '" + filePath\
                + "' where setting_name = 'excelSaveAsPath';")
        else:
            t1a = QtWidgets.QFileDialog.getSaveFileName(caption='另存为', \
                directory=str(time.strftime('%m_%d',time.localtime(time.time()))) + tableName, \
                filter=fileFilter)            
            filePath = os.path.split(t1a[0])[0]
            if len(filePath) > 0:
                self.sqliteObject.set('sqlStatement', "insert into main_settings values ('excelSaveAsPath','" + filePath + "',1);")
        try:
            self.sqliteObject.sqlExecute()
        except:
            pass
        
        fileName = os.path.split(t1a[0])[1]
        
        self.exportToExcel(datasList, self.printTemplate, filePath, fileName)

    def maniClassPrint(self, datasList):
        win32print.SetDefaultPrinter(self.comboBox_5.currentText())
        s2a = self.exportToExcel(datasList, self.printTemplate)
        if s2a != False:
            try:
                xlApp = win32com.client.Dispatch('Excel.Application')
                workbook = xlApp.Workbooks.open(os.path.split(os.path.abspath(sys.argv[0]))[0] + '\\' + s2a)
                workbook.PrintOut()
                workbook.Close(SaveChanges=0)
                del xlApp
            except:
                QtWidgets.QMessageBox.warning(self.centralWidget,'未知错误','打印失败!', QtWidgets.QMessageBox.Ok)
        win32print.SetDefaultPrinter(self.sysDefaultPrinter)
    
    def delClasslManifest(self, classManiTable):
        for n1a, o1a in enumerate(self.classManiObjectList):
            if o1a == classManiTable.parent():
                del self.classManiDataList[n1a]
                del self.classManiList[n1a]
                del self.classManiObjectList[n1a]
                classManiTable.parent().deleteLater()
                break
    
    def findAndSelectTableWidgetRow(self, tableWidgetObject, dataList, fieldIndex):
        queryStr = tableWidgetObject.parent().findChild(QtWidgets.QLineEdit).text()
        for n1a, l1a in enumerate(dataList):
            if l1a[fieldIndex] == queryStr:
                tableWidgetObject.selectRow(n1a)
                break
    
    def setMappingRule(self, workbookObject):
        pass

    def openSettingWindow(self, settingType, tableName, settingAlias):
        #渠道及国家完整列表
        completeCheckList = []
        sqlServerField = ''
        sqlServerTable = ''
        self.sqliteObject.set('sqlStatement', \
            "select * from setting_tables where setting_table_name = '"+ tableName +"';")
        if self.sqliteObject.sqlExecute() == True:
            c3a = True
            try:
                applyCurrentPage = bool(self.sqliteObject.sqlResult[0][3])
            except IndexError:
                c3a = False
            if c3a == False:
                self.sqliteObject.set('sqlStatement', "insert into setting_tables values('"\
                    + tableName + "','" + settingType + "','" + settingAlias + "',1);")
                self.sqliteObject.sqlExecute()
                if settingType == 'stringReplace':
                    self.sqliteObject.set('sqlStatement', "create table if not exists " + tableName\
                        + " (source_string nvarchar not null primary key,replaced_string nvarchar,is_used boolean);")
                elif settingType == 'checkList':
                    self.sqliteObject.set('sqlStatement', "create table if not exists " + tableName\
                        + " (checkItems nvarchar not null primary key,is_used boolean);")
                self.sqliteObject.sqlExecute()
                applyCurrentPage = True
            #创建设置窗口
            setupWindow = NewWindow()
            setupWindow.widget.setWindowTitle(settingAlias)
            setupWindow.widget.setLayout(QtWidgets.QVBoxLayout())
            #存放每行checkbox及item对象,表结构[...,每行[checkbox对象,原文本对象,替换文本对象],...]
            self.tableContentsList = []
            setupWindow.widget.layout().addLayout(QtWidgets.QHBoxLayout())
            checkBox2a = QtWidgets.QCheckBox(setupWindow.widget)
            checkBox2a.setText = '启用本页规则'
            checkBox2a.setChecked(applyCurrentPage)
            setupWindow.widget.layout().findChild(QtWidgets.QHBoxLayout).addWidget(checkBox2a)
            #添加按钮            
            pushButton3a = QtWidgets.QPushButton(setupWindow.widget)
            pushButton3a.setText("保存")
            pushButton3b = QtWidgets.QPushButton(setupWindow.widget)
            pushButton3b.setText("取消")
            setupWindow.widget.layout().findChild(QtWidgets.QHBoxLayout).addWidget(pushButton3a)
            setupWindow.widget.layout().findChild(QtWidgets.QHBoxLayout).addWidget(pushButton3b)
            spacerItem2a = QtWidgets.QSpacerItem(0, 0, QtWidgets.QSizePolicy.Expanding)
            setupWindow.widget.layout().findChild(QtWidgets.QHBoxLayout).addItem(spacerItem2a)
            tableWidget = QtWidgets.QTableWidget(setupWindow.widget)
            setupWindow.widget.layout().addWidget(tableWidget)
            #按钮连接槽            
            pushButton3b.clicked.connect(lambda:self.closeWindow(setupWindow.widget))
            if settingType == 'stringReplace':
                pushButton3c = QtWidgets.QPushButton(setupWindow.widget)
                pushButton3c.setText("下面插入新行")                
                pushButton3d = QtWidgets.QPushButton(setupWindow.widget)
                pushButton3d.setText("删除选中一行")
                pushButton3e = QtWidgets.QPushButton(setupWindow.widget)
                pushButton3e.setText("上移一行")
                pushButton3f = QtWidgets.QPushButton(setupWindow.widget)
                pushButton3f.setText("下移一行")            
                setupWindow.widget.layout().findChild(QtWidgets.QHBoxLayout).addWidget(pushButton3c)
                setupWindow.widget.layout().findChild(QtWidgets.QHBoxLayout).addWidget(pushButton3d)
                setupWindow.widget.layout().findChild(QtWidgets.QHBoxLayout).addWidget(pushButton3e)
                setupWindow.widget.layout().findChild(QtWidgets.QHBoxLayout).addWidget(pushButton3f)
                pushButton3a.clicked.connect(lambda:self.saveStrReplSetting(self.tableContentsList, tableName))
                pushButton3c.clicked.connect(lambda:self.addTableRow(tableWidget, tableWidget.currentRow() + 1, 2))
                pushButton3d.clicked.connect(lambda:self.removeTableRow(tableWidget, tableWidget.currentRow()))
                pushButton3e.clicked.connect(lambda:self.exchangeTableRow(tableWidget, tableWidget.currentRow(), 'UP'))
                pushButton3f.clicked.connect(lambda:self.exchangeTableRow(tableWidget, tableWidget.currentRow(), 'DOWN'))
                tableWidget.setColumnCount(3)
                tableWidget.setHorizontalHeaderItem(0, QtWidgets.QTableWidgetItem("本行生效"))
                tableWidget.setHorizontalHeaderItem(1, QtWidgets.QTableWidgetItem("原字句"))
                tableWidget.setHorizontalHeaderItem(2, QtWidgets.QTableWidgetItem("替换为"))                
            if settingType == 'checkList':
                pushButton3a.clicked.connect(lambda:self.saveCheckList(tableName, self.tableContentsList))
                tableWidget.setColumnCount(2)
                tableWidget.setHorizontalHeaderItem(0, QtWidgets.QTableWidgetItem("本行生效"))
                tableWidget.setHorizontalHeaderItem(1, QtWidgets.QTableWidgetItem("要检查项列表"))
                #读取渠道数据库数据
                if tableName == 'limitHubIn':
                    sqlServerTable = 'bHubIn'
                    sqlServerField = 'HubNO'
                elif tableName == 'limitHubOut':
                    sqlServerTable = 'bHubOut'
                    sqlServerField = 'HubNO'
                elif tableName == 'notifyDest':
                    sqlServerTable = 'bDest'
                    sqlServerField = 'DestName'
                self.sqlServerObject.set('sqlStatement',\
                    "select " + sqlServerField + " from " + sqlServerTable + ";")
                try:
                    self.sqlServerObject.sqlExecute()
                    completeCheckList = self.sqlServerObject.sqlResult
                except:
                    pass
            #读取数据并填入设置列表
            self.sqliteObject.set('sqlStatement', "select * from " + tableName)
            if self.sqliteObject.sqlExecute() == True:
                n3a = len(self.sqliteObject.sqlResult)
                if settingType == 'checkList':
                    for n5a, t5a in enumerate(completeCheckList):
                        self.addTableRow(tableWidget, n5a, 1)
                        c6a = False
                        if n3a > 0:
                            for l6a in self.sqliteObject.sqlResult:
                                if l6a[1] == 1:
                                    if l6a[0] == t5a[0]:
                                        self.tableContentsList[n5a][0].setChecked(True)
                                        c6a = True
                        if  c6a == False:
                            self.tableContentsList[n5a][0].setChecked(False)
                        self.tableContentsList[n5a][1].setText(t5a[0])
                elif settingType == 'stringReplace':
                    if n3a > 0:
                        #设置表单元格对象及数据
                        for n4a in range(n3a):
                            #添加一空行
                            self.addTableRow(tableWidget, n4a, 2)
                            #设置checkbox状态
                            if self.sqliteObject.sqlResult[n4a][2] == 1:
                                self.tableContentsList[n4a][0].setChecked(True)
                            self.tableContentsList[n4a][1].setText(self.sqliteObject.sqlResult[n4a][0])
                            try:
                                s4a = self.sqliteObject.sqlResult[n4a][1]
                                if len(s4a) > 0:
                                    self.tableContentsList[n4a][2].setText(s4a)
                            except:
                                pass
                else:
                    self.addTableRow(tableWidget, 0, 2)
                setupWindow.widget.show()
            else:
                return(False)
        else:
            return(False)
    def addTableRow(self, tableObject, rowIndex, itemColumnCount):
        tableObject.insertRow(rowIndex)
        #存放每行checkbox对象
        rowContentsList = []
        widget1a = QtWidgets.QWidget(tableObject)
        widget1a.setLayout(QtWidgets.QHBoxLayout())
        rowContentsList.append(QtWidgets.QCheckBox())        
        widget1a.layout().addWidget(rowContentsList[0])
        widget1a.layout().setAlignment(rowContentsList[0],QtCore.Qt.AlignCenter)
        tableObject.setCellWidget(rowIndex, 0, widget1a)
        #添加空白框
        rowContentsList.append(QtWidgets.QTableWidgetItem())
        tableObject.setItem(rowIndex, 1, rowContentsList[1])
        if itemColumnCount == 2:
            rowContentsList.append(QtWidgets.QTableWidgetItem())
            tableObject.setItem(rowIndex, 2, rowContentsList[2])
        self.tableContentsList.insert(rowIndex, rowContentsList)
        return(True)
        
    def removeTableRow(self, tableObject, rowIndex):        
        tableObject.removeRow(rowIndex)
        del self.tableContentsList[rowIndex]
    
    def exchangeTableRow(self, tableObject, rowIndex, direction):
        beAbleTo = False
        if direction.upper() == "UP":            
            if rowIndex > 0:
                beAbleTo = True
        else:
            if rowIndex < len(self.tableContentsList) - 1:
                beAbleTo = True
                rowIndex += 1
        if beAbleTo == True:
            b3a = self.tableContentsList[rowIndex - 1][0].isChecked()
            self.tableContentsList[rowIndex - 1][0].setChecked(self.tableContentsList[rowIndex][0].isChecked())
            self.tableContentsList[rowIndex][0].setChecked(b3a)
            s3a = self.tableContentsList[rowIndex - 1][1].text()
            self.tableContentsList[rowIndex - 1][1].setText(self.tableContentsList[rowIndex][1].text())
            self.tableContentsList[rowIndex][1].setText(s3a)
            s3b = self.tableContentsList[rowIndex - 1][2].text()
            self.tableContentsList[rowIndex - 1][2].setText(self.tableContentsList[rowIndex][2].text())
            self.tableContentsList[rowIndex][2].setText(s3b)
            return(True)
        else:
            return(False)
        
    def saveStrReplSetting(self, tableContentsList, tableName):
        pageIsUsed = False
        isTableEmpty = False
        #获取是否启用本页设置状态值，并保存到数据库
        try:
            pageIsUsed = tableContentsList[0][1].tableWidget().parent().findChild(QtWidgets.QCheckBox).isChecked()
            self.sqliteObject.set('sqlStatement', "update setting_tables set is_used="\
                + str(int(pageIsUsed)) + " where setting_table_name='" + tableName + "';")
            self.sqliteObject.sqlExecute()
        except IndexError:
            isTableEmpty = True
        #清空数据库表
        self.sqliteObject.set('sqlStatement', "delete from " + tableName + ";")
        try:
            self.sqliteObject.sqlExecute()
        except:
            pass
        self.sqliteObject.set('sqlStatement', "create table if not exists " + tableName
                + " (source_string nvarchar not null primary key,replaced_string nvarchar,is_used boolean);")
        try:
            self.sqliteObject.sqlExecute()
        except:
            return(False)
        if tableName == 'generalStringReplace':
            self.generalStringReplaceList = []
        else:
            self.customStringReplaceList = []
        if isTableEmpty == False:
            #读取tableWidget缓存list中的数据并保存到数据库及缓存list中
            for n1a in range(len(tableContentsList)):
                try:
                    if len(tableContentsList[n1a][1].text()) > 0:
                        if tableContentsList[n1a][0].isChecked() == True:
                            n5a = 1
                            if pageIsUsed == True:
                                if tableName == 'generalStringReplace':
                                    self.generalStringReplaceList.append([tableContentsList[n1a][1].text(), tableContentsList[n1a][2].text()])
                                else:
                                    self.customStringReplaceList.append([tableContentsList[n1a][1].text(), tableContentsList[n1a][2].text()])
                        else:
                            n5a = 0
                        self.sqliteObject.set('sqlStatement', "insert into " + tableName +\
                        " values('" + tableContentsList[n1a][1].text() + "','" + tableContentsList[n1a][2].text() + "'," + str(n5a) + ");")
                        self.sqliteObject.sqlExecute()                    
                except:
                    continue
        return(True)
    
    def saveCheckList(self, tableName, contentsList):
        try:
            self.sqliteObject.set('sqlStatement', "delete from " + tableName + ";")
            self.sqliteObject.sqlExecute()
        except:
            pass
        self.sqliteObject.set('sqlStatement', "create table if not exists " + tableName
                + " (checkItems nvarchar not null primary key,is_used boolean);")
        try:
            self.sqliteObject.sqlExecute()
        except:
            return(False)
        self.checkListDict[tableName][0].clear()
        self.checkListDict[tableName][1].clear()
        for n1a in range(len(contentsList)):
            try:
                if len(contentsList[n1a][1].text()) > 0:
                    if contentsList[n1a][0].isChecked() == True:
                        s5a = contentsList[n1a][1].text()
                        self.sqliteObject.set('sqlStatement', "insert into " + tableName +\
                        " values('" + s5a + "'," + str(1) + ");")
                        self.sqliteObject.sqlExecute()
                        self.checkListDict[tableName][0].append(s5a)
                        self.checkListDict[tableName][1].addItem(s5a)
            except:
                continue
        return(True)
    
    def closeWindow(self, widget):
        widget.close()
        widget.deleteLater()
        
    def initSettingDB(self, dbFile):
        self.sqliteObject = CommonFunctions.DBOption()
        self.sqliteObject.set('dbType', 'SQLITE')
        self.sqliteObject.set('dbPathAndName', dbFile)
        self.sqliteObject.set('sqlStatement', 'create table if not exists main_settings'\
            ' (setting_name nvarchar not null primary key,setting_value nvarchar,is_used boolean);')        
        popWarning = False
        try:
            if self.sqliteObject.sqlExecute() == False:
                popWarning = True
            else:
                self.sqliteObject.set('sqlStatement', 'create table if not exists setting_tables'
                    + ' (setting_table_name nvarchar not null primary key,setting_type nvarchar not null,alias_name nvarchar,is_used boolean);')
                if self.sqliteObject.sqlExecute() == False:
                    popWarning = True
        except:
            popWarning = True
        if popWarning == True:
            QtWidgets.QMessageBox.warning(self.centralWidget,\
            '数据库操作错误','初始化设置数据库失败!\n请检查sqlite及数据库文件'+dbFile, QtWidgets.QMessageBox.Ok)
            return(False)
        return(True)
        
    def readSettingDBTable(self, tableName, writedList, settingType, writeSeq = []):
        if settingType == 'stringReplace':
            allIsUsedField = 3
            recordIsUsedField = 2
        elif settingType == 'checkList':
            allIsUsedField = 3
            recordIsUsedField = 1
        self.sqliteObject.set('sqlStatement', 'select * from setting_tables where setting_table_name = "'+tableName+'";')
        try:
            self.sqliteObject.sqlExecute()
            if self.sqliteObject.sqlResult[0][allIsUsedField] == 1:        
                self.sqliteObject.set('sqlStatement', 'select * from ' + tableName + ';')
                try:            
                    self.sqliteObject.sqlExecute()
                    l2a = self.sqliteObject.sqlResult
                    for l2b in l2a:
                        if l2b[recordIsUsedField] == 1:
                            if settingType == 'stringReplace':
                                writedList.append([l2b[n5a] for n5a in writeSeq])
                            elif settingType == 'checkList':
                                writedList.append(l2b[writeSeq[0]])
                except:
                    return(False)
        except:
            return(False)
    
    #复制单号到剪贴板。传入参数为list或tableWidget对象+下标或列号列表
    def writeToClipboard(self, *datas):
        s1a = ''
        if type(datas[0]) == 'PyQt5.QtWidgets.QTableWidget':
            for n2a in range(datas[0].rowCount()):                
                s3a = ''
                for n3a, n3b in enumerate(datas[1]):
                    if n3a == 0:
                        s3a = datas[0].item(n2a,n3b).text()
                    else:
                        s3a = s3a + '\t' + datas[0].item(n2a,n3b).text()
                s1a = s1a + '\n' + s3a
        else:
            for l1a in datas[0]:
                s3a = ''
                for n3a, n3b in enumerate(datas[1]):
                    if n3a == 0:
                        s3a = str(l1a[n3b])
                    else:
                        s3a = s3a + '\t' + str(l1a[n3b])
                s1a = s1a + '\n' + s3a
        app.clipboard().setText(s1a.strip())

    def ttsSpeak(self, sqlResult, sqlResultSub, *replaceTable):
        speakString = sqlResult[sqlResultSub]
        try:
            if type(speakString) == bool:
                if speakString == True:
                    speakString = '是'
                else:
                    speakString = '否'
            else:
                if len(speakString) == 0:
                    self.tts.speak("无信息")
                    return(True)
                #替换字符串
                if len(replaceTable) != 0:
                    for l2a in replaceTable:
                        for l3a in l2a:
                            speakString = speakString.replace(l3a[0], l3a[1])
                #检查是否目的地提醒地区
                if len(speakString) > 0:            
                    if self.isDestNameNotify == 1:
                        for s3a in self.destNameNotifyList:
                            if sqlResult[6] == s3a:
                                self.tts.speak(s3a)
                                break
                else:
                    self.tts.speak("无信息")                
        except:
            self.tts.speak("无效信息")
            return(False)
        self.tts.speak(speakString)
        return(True)
    
class NewWindow(QtWidgets.QWidget):
    def __init__(self):
        super(NewWindow, self).__init__()
        self.widget = QtWidgets.QWidget()

mainApplication = MainApplication()
mainApplication.show()
#PySide2方法
#sys.exit(app.exec_())
sys.exit(app.exec())
